﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ClientRegister
{
    public class ClassInCourseProvider
    {
        ConnectDB con = new ConnectDB();
        SqlConnection cnn;
        SqlCommand cmd;
        SqlDataAdapter ada;

        //Them lop hoc vao khoa
        public int addClassInCourse(string idCourse, string idClass)
        {
            string query = string.Format("insert into ClassInCourse(IDCourse,IDClass,NewRecord,CreateDateTime) values('{0}','{1}',{2},'{3}')", idCourse, idClass, 1, DateTime.Now.ToString());
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            return cmd.ExecuteNonQuery();
        }

        //Load danh sach lop hoc cua khoa
        public DataSet loadClassOfCourse(string idCourse)
        {
            string query = string.Format("select Classes.ID, Classes.NameClass, Classes.IDSubject, Classes.IDTearcher, Classes.StudyDay, Classes.Room, " +
                "Classes.Time, Classes.Fee, Classes.StartDate, Classes.EndDate, Classes.AcceptRegister, Classes.Note, Classes.CreateDateTime "+
                "from Classes, ClassInCourse where Classes.ID = ClassInCourse.IDClass and ClassInCourse.IDCourse = '{0}'", idCourse);
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }

        //select and convert to xml
        public string selectClassInCourse(string idCourse, string idClass)
        {
            string query = string.Format("select * from ClassInCourse where IDCourse = '{0}' and IDClass = '{1}' for xml auto,elements", idCourse, idClass);
            string xmlstring = "";
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);

            xmlstring = cmd.ExecuteScalar().ToString();
            return xmlstring;
        }

        //Select ID cua record co cot new record = 1
        public DataSet SelectIDNewRecord()
        {
            string query = string.Format("select IDCourse,IDClass from ClassInCourse where NewRecord = 1");
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecord()
        {
            string query = string.Format("update ClassInCourse set NewRecord = 0 where NewRecord = 1");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        }

        //Select ID cua record co cot new record = 2
        public DataSet SelectIDNewRecordUpdate()
        {
            string query = string.Format("select IDCourse,IDClass from ClassInCourse where NewRecord = 2");
            DataSet ds = new DataSet();
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            ada = new SqlDataAdapter(cmd);
            ada.Fill(ds);
            return ds;
        }
        //Cap nhat lai cot newrecord sau khi dua len server
        public void UpdateNewRecordUpdate()
        {
            string query = string.Format("update ClassInCourse set NewRecord = 0 where NewRecord = 2");
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
        } 

        //Delete lop cua khoa hoc
        public void deleteClassInCourse(string idCourse, string idClass)
        {
            string query = string.Format("delete from ClassInCourse where IDCourse = '{0}' and IDClass = '{1}'", idCourse, idClass);
            cnn = con.Connect();
            cnn.Open();
            cmd = new SqlCommand(query, cnn);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}
